﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data.SqlClient;
using System.IO;

namespace WindowsForms
{
    class DBHelper
    {

        //private string connString =" Data Source=192.168.1.10;Initial Catalog = MyKTV; Persist Security Info=True;User ID = sa; Password=123456@qwe ";
        private string connString = "Data Source=127.0.0.1;Initial Catalog=FootballScheduleManager;Persist Security Info=True;User ID=sa;Password=123456@qwe";

        private SqlConnection connection;

        public SqlConnection Connection
        {

            get
            {

                if (connection == null)
                {
                    connection = new SqlConnection(connString);
                }
                return connection;
            }

        }


        public void OpenConnection()
        {

            if (Connection.State == System.Data.ConnectionState.Closed)
            {

                Connection.Open();

            }
            else if (Connection.State == System.Data.ConnectionState.Broken)
            {


                Connection.Close();
                Connection.Open();

            }

        }

        public void CloseConnection()
        {

            if (Connection.State == System.Data.ConnectionState.Open || Connection.State == System.Data.ConnectionState.Broken)
            {

                Connection.Close();


            }

        }

        /// <summary>
        /// 关闭DataReader和Connection
        /// </summary>
        /// <param name="sqlConnection"></param>
        /// <param name="sqlDataReader"></param>
        public void closeAll(SqlConnection sqlConnection, SqlDataReader sqlDataReader)
        {
            try
            {
                if (null != sqlDataReader)
                {
                    sqlDataReader.Close();
                }
                if (null != sqlConnection)
                {
                    sqlConnection.Close();
                }

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }


        /// <summary>
        /// 创建Connection对象并返回
        /// </summary>
        /// <returns></returns>
        public SqlConnection getConn()
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(connString);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return conn;
        }


        /// <summary>
        /// 查找数据库路径集并返回
        /// </summary>
        /// <param name="Lujin"></param>
        /// <returns></returns>
        public string updLJ(string Lujin)
        {
            SqlConnection connection = null;
            string sql = "select resource_path from resource_path where resource_type = '" + Lujin + "'";
            try
            {
                //1、创建连接
                connection = getConn();
                //2、打开连接
                connection.Open();
                //3、创建command
                SqlCommand sqlCommand = new SqlCommand(sql, connection);
                //4、执行sql
                Lujin = sqlCommand.ExecuteScalar().ToString();

                return Lujin;

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return Lujin;
            }
            finally
            {
                //关闭资源
                closeAll(connection, null);
            }
        }
        public void DeleteDirectory(string path)
        {
            DirectoryInfo dir = new DirectoryInfo(path);
            if (dir.Exists)
            {
                DirectoryInfo[] childs = dir.GetDirectories();
                foreach (DirectoryInfo child in childs)
                {
                    child.Delete(true);
                }
                dir.Delete(true);
            }
        }

        public Boolean updExecute(string sql)
        {
            Boolean flag = false;
            SqlConnection connection = null;
            try
            {
                //1、创建连接
                connection = getConn();
                //2、打开连接
                connection.Open();
                //3、创建command
                SqlCommand sqlCommand = new SqlCommand(sql, connection);
                //4、执行sql
                int result = sqlCommand.ExecuteNonQuery();
                if (result > 0)
                {
                    flag = true;
                }

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                //关闭资源
                closeAll(connection, null);

            }
            return flag;
        }


    }
}
